In database systems, a rule that limits the values that a particular set of data can hold is referred to as a constraint. For example, a constraint CNST1 may specify that all data in a column COL1 of a particular table must fall within a particular range RNG1. Standard types of constraints include not null, check, unique key, primary key, and foreign key constraints. These constraints are defined in the ANSI SQL '92 standard described in ISO/IEC 9075: 1992, Database Language SQL, Jul. 30, 1992.
Often, it is desirable to enable a constraint for a pre-existing body of data. Because the constraint was not enforced during the entry of the pre-existing body of data, some or all of the pre-existing data may violate the constraint. Therefore, the constraint is not enabled until the constraint has been validated.
Existing database systems enable a constraint by locking all constrained data, validating the constraint, and then unlocking the constrained data and enforcing the constraint on any future changes. FIG. 1 illustrates the conventional approach to enabling a constraint in greater detail.
Referring to FIG. 1, at step 100 the process that has been assigned the task of enabling the constraint (the "enablement process") obtains an exclusive lock on the constraint definition. Existing database systems typically wait up to some predetermined amount of time for the exclusive lock before aborting the operation. Obtaining an exclusive lock effectively prevents any other processes from reading or changing the constraint definition. At step 102, the enablement process obtains shared locks on the constrained data. This allows the enablement process to read the constrained data and prevents other processes from changing the constrained data.
At step 104, the enablement process validates the constraint by checking all of the constrained data for conformity with the constraint. If any data item in the constrained data violates the rule specified in the constraint, then the constraint is not valid and control proceeds from step 106 to step 108. At step 108, an error is generated and the enablement process is aborted. When the constraint is invalid, invalid data is optionally placed into an exceptions table before the enablement process is aborted.
If all of the constrained data conforms to the rule specified in the constraint, the constraint is valid and control proceeds from step 106 to step 110. At step 110, the enablement process does anything required to allow enforcement of the constraint. This may include, for example creating an index for a unique key constraint.
At step 112, the definition of the constraint is updated to record that the constraint is enabled. At step 114, the change to the constraint definition is committed. The constraint is not enabled unless this final commit occurs. At step 116, the locks on the constrained data and the constraint definition are released and the database system begins enforcing the constraint. Typically, steps 114 and 116 are performed as a single atomic operation.
Different database systems may employ slight variations of the constraint enablement technique described above. For example, the enablement process may obtain exclusive locks rather than share locks on the constrained data itself. Also, the enablement process may obtain an exclusive lock on the constraint definition after validating the constraint, rather than before validating the constraint.
A significant disadvantage of the prior art constraint enablement techniques is that inserts, updates, and deletes are disallowed on the constrained data while a constraint is being validated. If the existing body of data is large, validating a constraint may take hours. Such delays are unacceptable in many situations. For example, if the database is being used in a flight reservation system, travelers will not want to wait hours for their reservations to be confirmed.
One attempt to avoid the delay inherent in the conventional constraint enablement process allows users to specify constraints that are only enforced. A disabled constraint can become an enforced-only constraint by (1) obtaining an exclusive lock on the constraint definition, (2) doing anything required to allow enforcement of the constraint, (3) updating the constraint definition to record that the constraint is enforced but not enabled, (4) committing the change to the constraint definition, and (5) enforcing the constraint after the commit.
After an enforced-only constraint has been created, the user will know that all changes made and all data inserted after the creation of the constraint will conform to the constraint. However, the constraint is not actually validated. Therefore, the database engine has not proven that the data that existed before the creation of the constraint actually conforms to the constraint. Because some of the constrained data may actually violate an enforced-only constraint, enforced-only constraints have very limited utility. If the user requires an enforced-only constraint to be enabled, the user executes the enablement technique described above, thereby rendering the constrained data unavailable for modification by the user.
Based on the foregoing, it is clearly desirable to provide a mechanism for enabling constraints without making the constrained data unavailable for inserts, updates and deletes for an extended period of time.